import { Router, Request, Response } from 'express'
import { getDatabase } from '../database/init'

const router = Router()

// 导出所有任务数据
router.get('/tasks', async (req: Request, res: Response) => {
  try {
    if (!req.userId) {
      return res.status(401).json({ error: '用户未认证' })
    }
    
    const { format = 'json' } = req.query
    const db = getDatabase()
    
    // 获取所有任务和子任务
    const result = await db.query(`
      SELECT t.*, 
             COALESCE(
               json_agg(
                 CASE WHEN s.id IS NOT NULL THEN 
                   json_build_object('id', s.id, 'title', s.title, 'completed', s.completed)
                 END
               ) FILTER (WHERE s.id IS NOT NULL), 
               '[]'
             ) as subtasks
      FROM tasks t
      LEFT JOIN subtasks s ON t.id = s.task_id
      WHERE t.user_id = $1
      GROUP BY t.id
      ORDER BY t.created_at DESC
    `, [req.userId])
    
    const tasks = result.rows.map((row: any) => ({
      id: row.id,
      title: row.title,
      description: row.description,
      dueDate: row.due_date,
      priority: row.priority,
      category: row.category,
      tags: row.tags,
      completed: row.completed,
      notes: row.notes,
      isRecurring: row.is_recurring,
      recurringType: row.recurring_type,
      recurringPattern: row.recurring_pattern,
      createdAt: row.created_at,
      updatedAt: row.updated_at,
      subtasks: row.subtasks
    }))

    if (format === 'csv') {
      // CSV格式导出
      const csvHeader = 'ID,标题,描述,到期日期,优先级,分类,标签,完成状态,备注,创建时间,更新时间\n'
      const csvRows = tasks.map(task => [
        task.id,
        `"${task.title || ''}"`,
        `"${task.description || ''}"`,
        task.dueDate || '',
        task.priority,
        task.category,
        `"${task.tags?.join(';') || ''}"`,
        task.completed ? '已完成' : '未完成',
        `"${task.notes || ''}"`,
        task.createdAt,
        task.updatedAt
      ].join(','))
      
      const csvContent = csvHeader + csvRows.join('\n')
      
      res.setHeader('Content-Type', 'text/csv; charset=utf-8')
      res.setHeader('Content-Disposition', `attachment; filename=aide-tasks-${new Date().toISOString().split('T')[0]}.csv`)
      res.send('\uFEFF' + csvContent) // 添加BOM以支持中文
    } else {
      // JSON格式导出
      const exportData = {
        exportTime: new Date().toISOString(),
        version: '1.0.0',
        totalTasks: tasks.length,
        completedTasks: tasks.filter(t => t.completed).length,
        data: tasks
      }
      
      res.setHeader('Content-Type', 'application/json')
      res.setHeader('Content-Disposition', `attachment; filename=aide-tasks-${new Date().toISOString().split('T')[0]}.json`)
      res.json(exportData)
    }
  } catch (error) {
    console.error('导出任务数据失败:', error)
    res.status(500).json({ error: '导出数据失败' })
  }
})

// 导出用户统计数据
router.get('/stats', async (req: Request, res: Response) => {
  try {
    if (!req.userId) {
      return res.status(401).json({ error: '用户未认证' })
    }
    
    const db = getDatabase()
    
    // 获取基础统计
    const tasksResult = await db.query('SELECT * FROM tasks WHERE user_id = $1 ORDER BY created_at DESC', [req.userId])
    const tasks = tasksResult.rows
    
    const stats = {
      exportTime: new Date().toISOString(),
      overview: {
        totalTasks: tasks.length,
        completedTasks: tasks.filter(t => t.completed).length,
        pendingTasks: tasks.filter(t => !t.completed).length,
        completionRate: tasks.length > 0 ? (tasks.filter(t => t.completed).length / tasks.length * 100).toFixed(2) + '%' : '0%'
      },
      categoryStats: {},
      priorityStats: {},
      monthlyStats: {},
      tagStats: {}
    }
    
    // 分类统计
    const categories = tasks.reduce((acc: any, task) => {
      acc[task.category] = (acc[task.category] || 0) + 1
      return acc
    }, {})
    stats.categoryStats = categories
    
    // 优先级统计
    const priorities = tasks.reduce((acc: any, task) => {
      acc[task.priority] = (acc[task.priority] || 0) + 1
      return acc
    }, {})
    stats.priorityStats = priorities
    
    // 月度统计
    const monthlyData = tasks.reduce((acc: any, task) => {
      const month = task.created_at ? new Date(task.created_at).toISOString().substring(0, 7) : 'unknown' // YYYY-MM
      acc[month] = (acc[month] || 0) + 1
      return acc
    }, {})
    stats.monthlyStats = monthlyData
    
    // 标签统计
    const tagCounts: any = {}
    tasks.forEach(task => {
      if (task.tags && Array.isArray(task.tags)) {
        task.tags.forEach((tag: string) => {
          tagCounts[tag] = (tagCounts[tag] || 0) + 1
        })
      }
    })
    stats.tagStats = tagCounts
    
    res.json(stats)
  } catch (error) {
    console.error('导出统计数据失败:', error)
    res.status(500).json({ error: '导出统计数据失败' })
  }
})

// 导出重复任务模板
router.get('/recurring', async (req: Request, res: Response) => {
  try {
    if (!req.userId) {
      return res.status(401).json({ error: '用户未认证' })
    }
    
    const db = getDatabase()
    
    const result = await db.query(`
      SELECT * FROM recurring_tasks 
      WHERE is_active = TRUE AND user_id = $1
      ORDER BY created_at DESC
    `, [req.userId])
    
    const recurringTasks = result.rows.map((row: any) => ({
      id: row.id,
      title: row.title,
      description: row.description,
      priority: row.priority,
      category: row.category,
      tags: row.tags,
      notes: row.notes,
      recurringType: row.recurring_type,
      recurringPattern: row.recurring_pattern,
      isActive: row.is_active,
      createdAt: row.created_at,
      updatedAt: row.updated_at
    }))
    
    const exportData = {
      exportTime: new Date().toISOString(),
      version: '1.0.0',
      totalRecurringTasks: recurringTasks.length,
      data: recurringTasks
    }
    
    res.json(exportData)
  } catch (error) {
    console.error('导出重复任务失败:', error)
    res.status(500).json({ error: '导出重复任务失败' })
  }
})

export { router as exportRoutes }
